﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;



namespace WebTraderLiteCL
{
    public class DBTransactions
    {
        //initializers

        MySqlConnection conn;
        

        public DBTransactions()
        {

  

        }//default constructor

        //returns 0 for success
        public int OpenDB()
        {
            string ConnectionString = "server=localhost;uid=root;password=slang;database=webtraderlite;port=3306";
            conn = new MySqlConnection(ConnectionString);
            //conn.ConnectionString = "Driver={MySQL ODBC 5.2w Driver};server=localhost;uid=root;password=slang;database=webtraderlite;port=3306";
            
            try
            {
                conn.Open();
                // Process data here.
                //Console.WriteLine("connection sucessfull");
                Console.WriteLine("conn successful");
                return 0;
            }
            catch (Exception ex)
            {
                //Console.WriteLine("Failed to connect to data source" + ex);
                Console.WriteLine("conn unsuccessful" + ex);
                return 1;
            }
        }//end OpenDB

        public int CloseDB()
        {

            try
            {
                conn.Close();
                Console.WriteLine("conn Closed successful");
                return 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("conn Closed unsuccessful" + ex);
                return 1;
            } 
        }//end CloseDB




        public MySqlDataReader Query(string Query)
        {
            this.OpenDB();
            MySqlDataReader reader = null;
            try
            {
                MySqlCommand cmd = new MySqlCommand(Query, conn);
                reader = cmd.ExecuteReader();
                return reader;
                
            }//end try
            catch(MySqlException ex)
            {
                Console.WriteLine(ex);
            }//end catch

            this.CloseDB();
            return null;

        }//end Query


        //returns 0 for success
        public int AddNewUser(User auser)
        {
            this.OpenDB();
            String SQL = "Insert into User(loginname,firstname,lastname,address,email,password,zipcode,accBalance,accType,accStatus,regDate) Values ('" 
                + auser.getUserID() + "', '" + auser.getFirstName() + "', '" + auser.getLastName() + "', '" + auser.getUserAddress() + "', '" +
                auser.getEmail() + "', '" +auser.getPassword()+"','"+auser.getzipCode()+"',"+auser.getAccBalance()+",'"+auser.getaccType()+"','"+auser.getaccStatus()+"'," + " now());";

            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return 1;
        }//end AddNewUser

        //returns 0 for success
        public int deactivateAccount(string userID)
        {
            this.OpenDB();
            String SQL = "Update User set accStatus = 'Inactive' Where loginName = '" + userID + "'";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }


            return 1;
        }//end deactivateAccount


        //returns 0 for success
        public int reactivateAccount(string userID)
        {
            this.OpenDB();
            String SQL = "Update User set accStatus = 'Active' Where loginName = '" + userID + "'";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            return 1;
        }//end reactivateAccount

        //returns Balance of the User
        public float retrieveAccountBal(string userID)
        {
            this.OpenDB();
            String SQL = "Select accBalance from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);
            
            while (result.Read())
            {
                return result.GetFloat(0);
            }
            this.CloseDB();
            return (float)0.0;
            
          
        }//end retrieveAccountBal

        //returns string of user's zip
        public string retrieveZipCode(string userID)
        {
            this.OpenDB();
            String SQL = "Select zipcode from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";
        }//end retreiveZip

        //returns string of user's information
        public string retrieveAccountStatus(string userID)
        {
            this.OpenDB();
            String SQL = "Select accStatus from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();
            
            return "";
        }//end retreiveInfo

        public string retrieveAccountType(string userID)
        {
            this.OpenDB();
            String SQL = "Select accType from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";

        }//end retreiveInfo

        public string retrieveEmail(string userID)
        {
            this.OpenDB();
            String SQL = "Select email from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";
        }//end retreiveInfo

        public string retrieveFirstName(string userID)
        {
            this.OpenDB();
            String SQL = "Select firstName from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";
        }//end retreiveInfo

        public string retrieveLastName(string userID)
        {
            this.OpenDB();
            String SQL = "Select lastName from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";
        }//end retreiveInfo

        //this needs to change to DateTime or make the result into a string
        public DateTime retrieveRegistrationDate(string userID)
        {
            this.OpenDB();
            String SQL = "Select regDate from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetDateTime(0);
            }
            this.CloseDB();

            return (new DateTime(1111, 1, 1));
        }//end retreiveInfo

        public string retrieveUserAddress(string userID)
        {
            this.OpenDB();
            String SQL = "Select address from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";
        }//end retreiveInfo

        //returns string of user's Password
        public string retrievePswd(string userID)
        {
            this.OpenDB();
            String SQL = "Select password from User where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";
        }//end retrievePswd

        //returns 0 for success
        public int setBrokerFee(float amount)
        {
            this.OpenDB();
           
            String SQL = "Insert into fees(feeName,changeDate,fee) Values ('BrokerFee', now(), "+ amount + ");";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return 1;

        }//end amount

        public float getBrokerFee()
        {
            this.OpenDB();

            String SQL = "SELECT fee FROM fees where ChangeDate = (Select Max(ChangeDate) from fees)";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                float d = result.GetFloat(0);
                this.CloseDB();
                return d;
            }
            this.CloseDB();
            return (float)0.0;

        }//end amount

        //returns 0 for success
        public int suspendUser(GenUser user)
        {
            this.OpenDB();
            String SQL = "Update User set accStatus = 'Suspend' Where loginName = '" + user.getUserID() + "'";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }


            return 1;
        }//end suspendUser



        //returns 0 for success 
        public int unsuspend(GenUser user)
        {

            String SQL = "Update User set accStatus = 'Active' Where loginName = '" + user.getUserID() + "'";
            this.OpenDB();
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            return 1;
        }//end unsuspend

        //returns 0 for success 
        public int updateAccountBalance(GenUser user, float money)
        {
           // this.OpenDB();
            String SQL = "Select accBalance from User where loginName= '" + user.getUserID() + "'";
            float OldBalance = 0;
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                OldBalance = result.GetFloat(0);
            }
            //this.CloseDB();

            this.OpenDB();
            float NewBalance = OldBalance + money;
            String SQL2 = "Update User set accBalance = " + NewBalance + " Where loginName = '" + user.getUserID() + "'";
            MySqlCommand cmd = new MySqlCommand(SQL2, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            this.CloseDB();

            return 1;
        }//end updateAccounBalance


        public int updateUser(GenUser user)
        {
            this.OpenDB();
            String SQL = "Update User set firstName = '" + user.getFirstName()  + "', lastName = '" + user.getLastName() + "', address = '" + user.getUserAddress() + "', email = '" +
                user.getEmail() +"', zipcode = '"+user.getzipCode() + "'  Where loginName = '" + user.getUserID() + "'";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            this.CloseDB();
            return 1;
        }//end updateUser

        public int setPassword(string userID, string newPassword)
        {
            this.OpenDB();
            String SQL = "Update User set password = '" + newPassword + "'  Where loginName = '" + userID  + "'";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            this.CloseDB();
            return 1;
        }//end updateUser

        public List<Stock> retrieveMonitoredStocks(string userID)
        {
            this.OpenDB();
            String SQL = "Select stockSymbol from monitor where loginName= '" + userID + "'";
            MySqlDataReader result;
            result = Query(SQL);
            List<Stock> monitoredStocks = new List<Stock>();

            while (result.Read())
            {
                monitoredStocks.Add(new Stock( result.GetString(0) ) );            
            }
        
            this.CloseDB();
            return monitoredStocks;
            
        }
        public List<Stock> retriveSuggestionforStockSymbol(String query)
        {
            this.OpenDB();
            String modq="%";
            for(int i=0;i<query.Length;i++)
            {
                modq=modq+query[i]+"%";
            }
            modq=modq.ToUpper();
            string SQL = "select stockSymbol,companyName from stock where stockSymbol like '" + modq + "' or companyName like '" + modq+"' limit 0,2";
            MySqlDataReader result;
            result = Query(SQL);

            List<Stock> mystocks = new List<Stock>();
            int count = 0;
            while (result.Read() && count++<=2)
            {
                mystocks.Add(new Stock(result.GetString(0), result.GetString(1)));
            }
            this.CloseDB();
            return mystocks;

        }
        public void deleteMonitoredStock(string userName, string Symbol)
        {

            this.OpenDB();
            String SQL = "Delete from monitor where loginName= '" + userName + "'" + " and stockSymbol= '" + Symbol + "'";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
                        
        }


        public void insertIntoStock(Stock stk)
        {
            
            string sql = "Select stockSymbol from stock where stockSymbol= '" + stk.Symbol + "'";
            try
            {
                MySqlDataReader dt;
                dt = Query(sql);
                if (dt.HasRows)
                {
                    //NEED TO FIX IT.
                    /*this.OpenDB();
                    string x = "update stock set perSharePrice="+ stk.Price+" and noofShareOutstanding="+stk.SharesOutstanding+" where stockSymbol='" +
                    stk.Symbol +"' and " + "companyName= '" + stk.CompanyName.ToUpper()+ "'";
                    MySqlCommand cmd = new MySqlCommand(x, conn);
                    cmd.ExecuteNonQuery();
                    this.CloseDB();*/
                }
                else
                {
                    this.OpenDB();
                    string SQL = "insert into stock(stockSymbol, companyName, perSharePrice, noofShareOutstanding) values ('" + stk.Symbol + "', '" + stk.CompanyName.ToUpper()
                        + "'," + stk.Price + " ," + stk.SharesOutstanding + ")";
                    MySqlCommand cmd = new MySqlCommand(SQL, conn);
                    cmd.ExecuteNonQuery();
                    this.CloseDB();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            
                
            
        }
        public float retrievePurchasePrice(string userStockSym, string userID)
        {
            this.OpenDB();
            String SQL = "Select lastpurchasePrice from userstock where loginName= '" + userID + "'" + " and stockSymbol='" + userStockSym + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return float.Parse(result.GetString(0));
            }
            this.CloseDB();

            return 0;
        }

        public string retrieveSharesOwned(string userStockSym, string userID)
        {
            this.OpenDB();
            String SQL = "Select noofShare from userstock where loginName= '" + userID + "'" + " and stockSymbol='" + userStockSym + "'";
            MySqlDataReader result;
            result = Query(SQL);

            while (result.Read())
            {
                return result.GetString(0);
            }
            this.CloseDB();

            return "";
        }

        public bool inIntoUserStock(User usr, Stock stk, float no)
        {
           
            
            string sql="select loginname from userstock where loginname='"+usr.getUserID()+"' and stockSymbol='"+stk.Symbol.ToUpper()+"'";
            try
            {
                //check wheter this stock exists, if exists then update otherwise insert.
                MySqlDataReader result;
                result = Query(sql);
                if (result.HasRows)
                {
                    this.OpenDB();
                    string Sql = "update userstock set noofshare=noofshare+" + (Int32)(no) + "  where loginname='" + usr.getUserID() +
                        "' and " + "stockSymbol='" + stk.Symbol.ToUpper() + "'";
                    MySqlCommand cmd = new MySqlCommand(Sql, conn);
                    cmd.ExecuteNonQuery();
                    this.CloseDB();
                    return true;
                }
                else
                {
                    this.OpenDB();
                    string SQL = "insert into userstock(loginName,stockSymbol,lastpurchasePrice, noofShare) values" +
                                  "('" + usr.getUserID() + "','" + stk.Symbol.ToUpper() + "'," + stk.Price + "," + (Int32)(no) + ")";
                    try
                    {
                        MySqlCommand cmd = new MySqlCommand(SQL, conn);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.Write(ex);
                    }
                    this.CloseDB();
                    return true;
                }

            }
            catch (Exception ex)
            {
                Console.Write("ourside userstock insert"+ex);
            }
            this.CloseDB();
            return false;
        }

        public void debitBalance(User usr, Stock stck, float no)
        {
            this.OpenDB();
            string SQL = "update user set accBalance=accBalance-" + (stck.Price * no) + "where loginname='" + usr.getUserID() + "'";
            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            this.CloseDB();
        }

        public void insertIntoHistory(User usr, Stock stk, float p, string eventx)
        {
            this.OpenDB();
            string SQL = "insert into history(loginName, historyType, stock, numShares, price, dateofEvent) values('" +
                usr.getUserID() + "','" + eventx + "', '" + stk.Symbol.ToUpper() + "'," + (Int32)(p) + "," + stk.Price + ",now()" + ")";
            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            this.CloseDB();
        }

        public List<int> getAvailableUserShare(User usr, Stock stk)
        {
            string sql="select noofshare from userstock where loginname='"+usr.getUserID()+"' and stockSymbol='"+stk.Symbol.ToUpper()+"'";
            List<Int32> mystocks = new List<int>();
            try
            {
                //check wheter this stock exists, if exists then update otherwise insert.
                MySqlDataReader result;
                result = Query(sql);


                while (result.Read())
                {
                    mystocks.Add((Int32)result.GetFloat(0));
                }
                this.CloseDB();
                return mystocks;
            }
            catch (Exception ex)
            {

            }
            return mystocks;
        }

        public bool updateStokAfterSell(User usr, Stock stk,  float no)
        {
            this.OpenDB();
            string Sql = "update userstock set noofshare=noofshare-" + (Int32)(no) + "  where loginname='" + usr.getUserID() +
                "' and " + "stockSymbol='" + stk.Symbol.ToUpper() + "'";
            try
            {
                MySqlCommand cmd = new MySqlCommand(Sql, conn);
                cmd.ExecuteNonQuery();
                
                return true;
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            this.CloseDB();
            return false;
        }

        public void creditBalance(User usr, Stock stck, float no)
        {
            this.OpenDB();
            string SQL = "update user set accBalance=accBalance+" + (stck.Price * no) + "where loginname='" + usr.getUserID() + "'";
            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            this.CloseDB();
        }

        
        public List<Stock> getUserStocks(User usr)
        {
            string sql = "select stockSymbol, lastpurchasePrice, noofShare from userstock where loginname='" + usr.getUserID() + "'";
            List<Stock> mystocks = new List<Stock>();
            try
            {
                //check wheter this stock exists, if exists then update otherwise insert.
                MySqlDataReader result;
                result = Query(sql);

                
                while (result.Read())
                {
                    Stock stk = new Stock(result.GetString(0), result.GetFloat(1), result.GetInt32(2));
                    mystocks.Add(stk);
                }
                this.CloseDB();
                return mystocks;
            }
            catch (Exception ex)
            {

            }
            return mystocks;
        }

        public void insertIntoAutoBuy(Stock stock, int nshare, string username, string trend, float buyprice)
        {
            this.OpenDB();
            string SQL = "insert into autobuy(loginname,stocksymbol,purchasePrice,noofshare,id,trendtomonitor) values " +
                    "('" + username.ToUpper() + "','" + stock.Symbol.ToUpper() + "'," + buyprice + "," + nshare + ",now()+0"+",'"
                    + trend + "')";
            MySqlCommand cmd = new MySqlCommand(SQL,conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

        }

        public void deductandAddbrokerFees(string user)
        {
            float d=getBrokerFee();

            this.OpenDB();
            string SQL = "update user set accBalance=accBalance-" + d + " where loginName='" + user + "'";
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            try
            {
                cmd.ExecuteNonQuery();
                string sql = "update user set accBalance=accBalance+" + d + " where loginName= '" + "john" + "'";
                MySqlCommand cmdy = new MySqlCommand(sql, conn);
                cmdy.ExecuteNonQuery();

            }
            catch(Exception ex)
            {
                Console.WriteLine(ex);
            }
            this.CloseDB();

        }
        public void insertIntoAutoSell(Stock stock, int nshare, string username, string trend, float sellprice)
        {
            this.OpenDB();
            string sql = "select userstockid from userstock where loginname='" + username + "' and stocksymbol='" + stock.Symbol.ToUpper() + "'";
            MySqlDataReader reader;
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                reader = cmd.ExecuteReader();
                int userstockid = 0;
                while (reader.Read())
                {
                    userstockid = reader.GetInt32(0);
                }
                this.CloseDB();
                this.OpenDB();
                string SQL = "insert into autosell(userstockid,sellprice,noofshare,trendtoMonitor,id) values (" + userstockid + "," + sellprice + "," + nshare + ",'" +
                    trend + "', now()+0) ";
                MySqlCommand cmdx = new MySqlCommand(SQL, conn);
                cmdx.ExecuteNonQuery();
                this.CloseDB();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
        public void insertIntoMonitor(string userName, string stockSymbol)
        {
            this.OpenDB();
            string SQL = "insert into monitor(loginName, stockSymbol) values('" + userName + "','" + stockSymbol + "')";

            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            this.CloseDB();
        }    
    
    }//end Class DBTransactions
}